﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model.TrainModelHelper;
using System.Configuration;
namespace DAL.TrainDALhelp
{
    public class TrainRecordDAL
    {

        private static string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        private static SqlConnection conn = new SqlConnection(ConStr);
        public List<TrainRecordModel> TrainRecordList()
        {
            conn.Open();
            SqlCommand comd = new SqlCommand("select * from TrainTypeTable,TrainNatureTable,TrainMethodTable,TrainRecordTable,EmployeeLoginInfo_Tab,Dept_Tab where TrainTypeTable.TrainType_Id=TrainRecordTable.TrainType_Id and TrainRecordTable.TrainNature_Id =TrainNatureTable.TrainNature_Id and TrainRecordTable.TrainMethod_Id = TrainMethodTable.TrainMethod_Id and TrainRecordTable.Sponsor_Id= Dept_Tab.DeptID and TrainRecordTable.PropleId=EmployeeLoginInfo_Tab.EmployID", conn);
            SqlDataReader read = comd.ExecuteReader();
            List<TrainRecordModel> lis = new List<TrainRecordModel>();
            while (read.Read())
            {
                TrainRecordModel str = new TrainRecordModel();
                //类型
                str.TrainType_Id = Convert.ToInt32(read["TrainType_Id"]);
                str.TrainType_Name = read["EmployName"].ToString();
                //员工
                str.PropleId = Convert.ToInt32(read["PropleId"]);
                //str.Prople_Name = read["Prople_Name"].ToString();
                //分数
                //str.Grad_Id = Convert.ToInt32(read["Retul_id"]);
                //str.Score1 = decimal.Parse(read["Score1"].ToString());
                //部门
                str.Sponsor_Id = Convert.ToInt32(read["Sponsor_Id"]);
                str.Sponsor_Name = read["DeptName"].ToString();
                //类别
                str.TrainMethod_Id = Convert.ToInt32(read["TrainMethod_Id"]);
                str.TrainMethod_Name = read["TrainMethod_Name"].ToString();
                //方式
                str.TrainNature_Id = Convert.ToInt32(read["TrainNature_Id"]);
                str.TrainNature_Name = read["TrainNature_Name"].ToString();
                //表ID
                str.TrainRecord_Id = Convert.ToInt32(read["TrainRecord_Id"]);




                str.Principal_Name = read["Principal_Name"].ToString();
                str.TrainRecord_GUId = read["TrainRecord_GUId"].ToString();
                str.TrainRecord_Adder = read["TrainRecord_Adder"].ToString();
                str.TrainRecord_Cost = decimal.Parse(read["TrainRecord_Cost"].ToString());
                str.TrainRecord_Name = read["TrainRecord_Name"].ToString();

                str.TrainRecord_OverTime = DateTime.Parse(read["TrainRecord_OverTime"].ToString());
                str.TrainRecord_BeginTime = DateTime.Parse(read["TrainRecord_BeginTime"].ToString());




                lis.Add(str);


            }
            read.Close();
            conn.Close();
            return lis;

        }
        public int TrainRecordAdd(TrainRecordModel s1)
        {
            conn.Open();
            SqlCommand comd = new SqlCommand(string.Format("insert   into TrainRecordTable values ({0},{1},'{2}','{3}','{4}','{5}',{6},{7},{8},'{9}','{10}','{11}') ", s1.TrainType_Id, s1.TrainNature_Id, s1.TrainRecord_Name, s1.TrainRecord_BeginTime, s1.TrainRecord_OverTime, s1.TrainRecord_Cost, s1.PropleId, s1.Sponsor_Id, s1.TrainMethod_Id, s1.Principal_Name, s1.TrainRecord_GUId, s1.TrainRecord_Adder), conn);
            var i = comd.ExecuteNonQuery();
            conn.Close();
            return i;




        }
        public int PropleIdCount()
        {
            conn.Open();
            SqlCommand comd = new SqlCommand("select COUNT(sponsor_ID) FROM TrainRecordTable", conn);
            var I = comd.ExecuteScalar();
            conn.Close();
            return Convert.ToInt32(I);
        }
        public int TrainRecord_CostCount()
        {
            conn.Open();
            SqlCommand comd = new SqlCommand("select sum(TrainRecord_Cost) FROM TrainRecordTable", conn);
            var I = comd.ExecuteScalar();
            conn.Close();
            return Convert.ToInt32(I);
        }
    }
}

